﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using UFC.DBUtility;
using System.Collections;
using System.Configuration;
using Newtonsoft.Json;
using System.IO;
using System.Threading.Tasks;

namespace SendIndustrialNetwork
{
    public class SaveJxMESData
    {
        public static string toAppId = ConfigurationManager.AppSettings["ToAppID_JX"].ToString();//推送数据中心目的ID（机匣）
        public string filePathUrl = ConfigurationManager.AppSettings["filePathUrl"].ToString();//工艺附件存储站点地址
        public static string IotApiUrl = ConfigurationManager.AppSettings["IotApiUrl"].ToString();//IOT内网接口地址
        public pushTableDataToDC push = new pushTableDataToDC();
        public void Excute()
        {
            //SaveJXWorkflowStepData();//工序级任务
            //SaveJXWorkflowStepStatus2();//工序任务状态
            //SendJsonToJX();//推送Json文件至数据中心，在下发到机匣产线
            //SaveIOTWorkflowStepData();//IOT数控程序工序级任务
            SendIotEmployeeData();//IOT分厂人员信息下发
        }

        public void SendJsonToJX() 
        {
            string sql = @"select st.oldvalue jsonfileurl,ss.toappid,ss.id from sync_tablechangerecord st
                        left join sync_sendtoappid ss on ss.parentid =st.id
                        where ss.toappid IN('{0}') and ss.ismeshandle ='0' and st.oldvalue is not null";
            DataTable dt = OracleHelper.QueryTable(string.Format(sql, toAppId), null);
            Parallel.ForEach(dt.Select(), item =>
            {
                if (File.Exists(item["jsonfileurl"].ToString()))
                {
                    var sendType = item["jsonfileurl"].ToString().Contains("RW") ? "SyncOrder" : "SyncOrderStatus";
                    push.ExcuteFile(item["jsonfileurl"].ToString(), null, item["toAppId"].ToString(), sendType, item["id"].ToString());//SyncOrderStatus
                    OracleHelper.ExecuteSql(string.Format("update sync_sendtoappid set ismeshandle=1 where id='{0}'", item["id"].ToString()));
                }
            });
        }
        


        #region 工序级任务信息

        public void SaveJXWorkflowStepData()
        {
            DataTable apsWorkflowStep = getJxApsWorkflowStepData();
            foreach (DataRow row in apsWorkflowStep.Rows)
            {
                DataRow mdtRow = null;
                var data = getWorkflowStepData(row,"1", out mdtRow);
                if (mdtRow != null && !string.IsNullOrWhiteSpace(mdtRow["PROCESS_NO"].ToString()))
                {
                    InsertJsonToTableChangeRecord(data, mdtRow,"RW",toAppId);
                }
            }
        }
        /// <summary>
        /// 获取需要工序排产下发到机匣产线系统的工序级任务信息
        /// </summary>
        /// <returns></returns>
        private DataTable getJxApsWorkflowStepData()
        {
            string sql = @"select cdis.containerstepdispatchid ,cdis.containerid,apd.apsplandetailid,c2.customobjectname productline
                        ,CASE WHEN (SELECT COUNT(1) FROM customobjectitems c WHERE c.customobjectid=c2.customobjectid AND c.customobjectitemsname=cc.productname)>0 THEN 'X' ELSE 'D' END tasktype
                        from containerstepdispatch cdis 
                        left join apsplandetail apd on apd.containerid =cdis.containerid and apd.workflowstepid =cdis.workflowstepid
                        left join resourcedef r on r.resourceid =apd.resourceid
                        left join customobject c2 on c2.customobjectid=r.productline 
                        left join containercurrentinfo cc on cc.containerid =cdis.containerid 
                        where c2.customobjectname like 'CX-机匣柔性线' and apd.isuse=0 and cdis.isuse=0 and apd.status =1
                        and apd.createdate >sysdate - 300 and cc.containertype =7
                        and not exists (select 1 from sync_tablechangerecord st left join sync_sendtoappid ss on ss.parentid =st.id where to_char(st.newvalue)=apd.apsplandetailid and st.operatetype='1' and ss.toappid='{0}')
                        order by cdis.createdate
                       ";
            return OracleHelper.QueryTable(string.Format(sql), null);
        }
        /// <summary>
        /// 获取工序排产下发任务级数据
        /// </summary>
        /// <param name="apsplandetailid"></param>
        /// <param name="mdtRow"></param>
        /// <returns></returns>
        private string getWorkflowStepData(DataRow row, string tasktype,out DataRow mdtRow)
        {
            var select = @"select m.mfgordername,ds.containerstepdispatchid,s.specno,s.specid,sb2.specname,ds.qty,to_char(ds.plannedstartdate,'yyyy-MM-dd hh24:mi:ss') plannedstartdate,to_char(ds.plannedfinishdate,'yyyy-MM-dd hh24:mi:ss') plannedfinishdate,c.ContainerName
                        ,empl.fullname,empl.employeename,c.productid,c.productname,c.productdescription,c.productrevision ,w.part_revid,w.process_no,w.workflowrevision,w.ly2sequence,
                        ws.makecycle,ws.preparecycle,ws.transfercycle,dis.resourceid,r.resourcename ,r.description,'' resourceattribute,dis.workflowstepid,dis.apsplandetailid,1 operatetype
                        from apsplandetail  dis
                        left join containerstepdispatch ds on ds.containerid =dis.containerid and ds.workflowstepid =dis.workflowstepid and ds.isuse=0
                        left join containercurrentinfo c on c.containerid =dis.containerid 
                        left join mfgorder m on m.mfgorderid =c.mfgorderid 
                        left join workflowstep ws on ws.workflowstepid =dis.workflowstepid 
                        left join specbase sb on sb.specbaseid = ws.specbaseid
                        left join spec s on s.specid = nvl(sb.revofrcdid,ws.specid)
                        left join specbase sb2 on sb2.specbaseid = s.specbaseid
                        left join employee empl on empl.employeeid=ds.employeeid
                        left join workflow w on w.workflowid =ws.workflowid 
                        left join resourcedef r on r.resourceid =dis.resourceid
                        where  dis.apsplandetailid ='{0}'";
            var dt = OracleHelper.QueryTable(string.Format(select, row["apsplandetailid"].ToString()), null);
            DataRow dr = dt.Rows.Count > 0 ? dt.Rows[0] : null;
            mdtRow = dr;

            Dictionary<string, Object> data = new Dictionary<string, object>();
            if (!string.IsNullOrWhiteSpace(tasktype))
            {
                data.Add("ContainerName", dr == null ? "" : dr["ContainerName"]);//批次号
            }
            data.Add("MfgOrderName", dr == null ? "" : dr["mfgordername"]);//订单号
            data.Add("ContainerStepDispatchID", dr == null ? "" : dr["containerstepdispatchid"]);//派工ID
            data.Add("State","");//工序状态
            data.Add("SpecNO", dr == null ? "" : dr["specno"]);//工序序号
            data.Add("SPECID", dr == null ? "" : dr["specid"]);//工序唯一标识
            data.Add("OPDesc", dr == null ? "" : dr["specname"]);//工序名称
            data.Add("Qty", dr == null ? "" : dr["qty"]);//在制数量
            data.Add("StartTime", dr == null ? "" : dr["plannedstartdate"]);//计划开始时间
            data.Add("Endtime", dr == null ? "" : dr["plannedfinishdate"]);//计划结束时间
            data.Add("EmployeeName", dr == null ? "" : dr["employeename"]);//派工人员工号
            data.Add("FullName", dr == null ? "" : dr["fullname"]);//派工人员名称
            data.Add("ProductID", dr == null ? "" : dr["productid"]);//零件ID
            data.Add("ProductName", dr == null ? "" : dr["productname"]);//零件号
            data.Add("Description", dr == null ? "" : dr["productdescription"]);//零件名称
            data.Add("ProductRevision", dr == null ? "" : dr["productrevision"]);//零件版本
            data.Add("Part_RevID", dr == null ? "" : dr["part_revid"]);//设计图版次
            data.Add("Process_no", dr == null ? "" : dr["process_no"]);//工艺规程编号
            data.Add("WorkflowRevision", dr == null ? "" : dr["workflowrevision"]);//工艺规程版本
            data.Add("ly2sequence", dr == null ? "" : dr["ly2sequence"]);//工艺规程版次
            data.Add("MakeCycle", dr == null ? "" : dr["makecycle"]);//单件工时
            data.Add("PrepareCycle", dr == null ? "" : dr["preparecycle"]);//准备工时
            data.Add("TransferCycle", dr == null ? "" : dr["transfercycle"]);//运转工时
            data.Add("dispatchResourceName", dr == null ? "" : dr["resourcename"]);//派工设备编码
            data.Add("dispatchDescription", dr == null ? "" : dr["description"]);//派工设备名称
            data.Add("ResourceAttribute", dr == null ? "" :string.IsNullOrWhiteSpace(dr["resourceattribute"].ToString())?"":row["productline"]);//关联产线
            if (!string.IsNullOrWhiteSpace(tasktype))
            {
                data.Add("Steps", getStepInfoData(dr["workflowstepid"].ToString()));
                var fileurls = getworkflowFiles(dr["specid"].ToString());
                data.Add("FileUrls", string.IsNullOrWhiteSpace(fileurls) ? new string[0] : fileurls.Split(','));
            }
            data.Add("NC_Name", getWorkflowStepNC(dr));//NC_name数组
            data.Add("TaskType", row["tasktype"].ToString());
            return JsonConvert.SerializeObject(data);
        }
        /// <summary>
        /// 获取工序下的工装、辅材信息
        /// </summary>
        /// <param name="workflowStepID"></param>
        /// <returns></returns>
        private List<Dictionary<string, Object>> getStepInfoData(string workflowStepID)
        {
            List<Dictionary<string, Object>> stepList = new List<Dictionary<string, object>>();
            var selectStep = @"select sob.stepinfoname,so.stepinfoid,so.revision,so.stepseq,so.stepno,st.spectypename,ws.workflowstepname,so.workflowstepid,so.stepno||'-'||so.description stepinfodesc
                                from stepinfobase sob
                                left join stepinfo so on so.stepinfoid = sob.revofrcdid  
                                left join workflowstep ws on ws.workflowstepid=so.workflowstepid 
                                left join spectype st on st.spectypeid = so.spectypeid 
                                where so.workflowstepid='{0}'  
                                order by ws.sequence,so.stepno,so.stepseq ASC ";
            var selectCheckPoint = @"select wfs.workflowstepname,sc.basevalue,sc.floorfloat,sc.ceilfloat,sc.bubblenumber ,sc.workflowstepid,sc.stepinfoid,sc.singlesign,sc.checkno,sc.stepcheckpointid,sc.inputnum checkfrep,sc.CheckMethod
                                 from stepcheckpoint sc
                                 inner join workflowstep wfs on sc.workflowstepid=wfs.workflowstepid
                                 where sc.stepinfoid='{0}'
                                 order by sc.bubblenumber ";
            var selectCuttool = @"select t.sequence,t.qty, ct.cuttoolid toolid, ct.cuttoolname toolname,ct.drawingno||'/'||ct.specification ,ct.description,rt.resourcetypename 
                               from stepinfotools t
                               left join cuttool ct  on ct.cuttoolid = t.tools
                               left join resourcetype rt  on rt.resourcetypeid = ct.resourcetypeid
                               where ct.cuttoolid <> ' ' and t.stepinfoid='{0}'";
            var selectSpecialtool = @"select t.sequence,t.qty, st.specialtoolid toolid, st.specialtoolname toolname,st.drawingno||'/'||st.specification,st.description,rt.resourcetypename 
                           from stepinfotools t
                           left join specialtool st  on st.specialtoolid = t.tools
                           left join resourcetype rt  on rt.resourcetypeid = st.resourcetypeid
                           where st.specialtoolid <> ' ' and t.stepinfoid='{0}'";

            //工序下的检测项信息
            var stepDT = OracleHelper.QueryTable(string.Format(selectStep, workflowStepID), null);
            foreach (DataRow dr in stepDT.Rows)
            {
                Dictionary<string, Object> step = new Dictionary<string, object>();
                step.Add("StepInfoID", dr["stepinfoid"]);//工步ID
                step.Add("StepSeq", dr["stepseq"]);//工步顺序
                step.Add("StepNO", dr["stepno"]);//工步号
                step.Add("Description", dr["stepinfodesc"]);//工步名称
                step.Add("SpecType", "");
                //step.Add("stepinfodesc", dr["stepinfodesc"]);

                //工步下的检测项信息
                var checkpointDT = OracleHelper.QueryTable(string.Format(selectCheckPoint, dr["stepinfoid"]), null);
                List<Dictionary<string, Object>> checkpointList = new List<Dictionary<string, object>>();
                foreach (DataRow cpdr in checkpointDT.Rows)
                {
                    Dictionary<string, Object> cp = new Dictionary<string, object>();
                    cp.Add("StepCheckPointID", cpdr["stepcheckpointid"]);//检测项ID
                    cp.Add("CheckNO", cpdr["checkno"]);//顺序号
                    cp.Add("BubbleNumber", cpdr["bubblenumber"]);//气泡号
                    cp.Add("BaseValue", cpdr["basevalue"]);//理论值
                    cp.Add("FloorFloat", cpdr["floorfloat"]);//上公差
                    cp.Add("CeilFloat", cpdr["ceilfloat"]);//下公差
                    cp.Add("SingleSign", cpdr["singlesign"]);//单向标识
                    cp.Add("CheckFrep", cpdr["checkfrep"]);//检测次数
                    cp.Add("CheckMethod", cpdr["CheckMethod"]);//检测手段
                    checkpointList.Add(cp);
                }
                step.Add("DetectionItemIds", checkpointList);

                //工步下的刀具
                var cuttoolDT = OracleHelper.QueryTable(string.Format(selectCuttool, dr["stepinfoid"]), null);
                List<Dictionary<string, Object>> cuttoolList = new List<Dictionary<string, object>>();
                foreach (DataRow ctdr in cuttoolDT.Rows)
                {
                    Dictionary<string, Object> ct = new Dictionary<string, object>();
                    ct.Add("CutToolID", ctdr["toolid"]);//唯一ID
                    ct.Add("CutToolName", ctdr["toolname"]);//刀具资源编号
                    ct.Add("Description", ctdr["description"]);//刀具名称(描述) 刀具资源名称
                    ct.Add("Qty", ctdr["qty"]);//刀具资源数量
                    //ct.Add("resourcetypename", ctdr["resourcetypename"]);//工具类型
                    //ct.Add("sequence", ctdr["sequence"]);//顺序号
                    cuttoolList.Add(ct);
                }
                step.Add("ToolList", cuttoolList);
                //工步下的夹模具
                var specialtoolDT = OracleHelper.QueryTable(string.Format(selectSpecialtool, dr["stepinfoid"]), null);
                List<Dictionary<string, Object>> specialtoolList = new List<Dictionary<string, object>>();
                foreach (DataRow stdr in specialtoolDT.Rows)
                {
                    Dictionary<string, Object> st = new Dictionary<string, object>();
                    st.Add("SpecialToolID", stdr["toolid"]);//唯一ID
                    st.Add("SpecialToolName", stdr["toolname"]);//刀具名称
                    st.Add("Description", stdr["description"]);//刀具描述
                    st.Add("Qty", stdr["qty"]);//数量
                    //st.Add("resourcetypename", stdr["resourcetypename"]);//工具类型
                    //st.Add("sequence", stdr["sequence"]);//顺序号
                    specialtoolList.Add(st);
                }
                step.Add("FixtureAssets", specialtoolList);
                stepList.Add(step);
            }
            return stepList;
        }
        /// <summary>
        /// 根据工序ID查询工艺附件
        /// </summary>
        /// <param name="workflowStepID">工序ID</param>
        /// <returns></returns>
        private string getworkflowFiles(string workflowStepID)
        {
            var selectFile = @"SELECT wm_concat('{0}'||REPLACE(o.FILEPATH,'Temp/','')) filepath FROM OBJECTDOCINFO o WHERE o.PARENTID ='{1}'";
            var fileDt = OracleHelper.QueryTable(string.Format(selectFile, filePathUrl, workflowStepID), null);
            return fileDt.Rows.Count > 0 ? fileDt.Rows[0]["filepath"].ToString() : null;
        }
        /// <summary>
        /// 根据工序号、工艺规程及版本、件号及版本查询NC_NAME
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private string[] getWorkflowStepNC(DataRow dr) {
            var selectNC = @"select wm_concat(n.NC_NAME) NC_NAME from ncprogram n 
                    where regexp_like(DECODE(lengthb(n.specno),1,'000',2,'00',3,'0',4,'','')||n.specno,'{0}') and n.process_no ='{1}' and n.productname ='{2}' and n.productrevision ='{3}'";
            var fileDt = OracleHelper.QueryTable(string.Format(selectNC, dr["specno"], dr["process_no"], dr["productname"], dr["productrevision"]), null);
            return fileDt.Rows.Count > 0 && !string.IsNullOrWhiteSpace(fileDt.Rows[0]["NC_NAME"].ToString()) ? fileDt.Rows[0]["NC_NAME"].ToString().Split(',') : new string[0];
        }

        #endregion

        #region "工序任务状态"
        /// <summary>
        /// 工序任务状态变更
        /// </summary>
        private void SaveJXWorkflowStepStatus() 
        {
            string sql = @"select distinct st.operatetype, 
                        cci.mfgordername,c3.containerstepdispatchid,s.specno,s.specid,sb.specname,cci.containername,
                        cci.productid,cci.productname,cci.productdescription,cci.productrevision,apd.apsplandetailid,st.createtime
                        from sync_tablechangerecord st 
                        left join apsplandetail apd on apd.apsplandetailid =st.uniquecolvalue 
                        left join resourcedef r on r.resourceid =apd.resourceid
                        left join customobjectitems c on c.customobjectitemsid =r.productline
                        left join customobject c2 on c2.customobjectid=c.customobjectid
                        left join containercurrentinfo cci on cci.containerid =apd.containerid
                        left join containerstepdispatch c3 on c3.containerid =apd.containerid and c3.workflowstepid =apd.workflowstepid and c3.isuse =0
                        left join workflowstep ws on ws.workflowstepid =apd.workflowstepid 
                        left join specbase sb on sb.specbaseid = ws.specbaseid
                        left join spec s on s.specid = nvl(sb.revofrcdid,ws.specid)
                        where c2.customobjectname like '%机匣产线%' and st.operatetype<>1
                        and apd.createdate >sysdate - 300
                        and not exists (select 1 from sync_tablechangerecord stt where to_char(stt.newvalue)=apd.apsplandetailid and stt.operatetype=st.operatetype)
                        order by st.createtime  
                        ";
            DataTable dt = OracleHelper.QueryTable(sql,null);
            foreach(DataRow row in dt.Rows)
            {
                Dictionary<string, object> dic = new Dictionary<string, object>();
                dic.Add("MfgOrderName", row["mfgordername"].ToString());//订单号
                dic.Add("ContainerName", row["containername"].ToString());//批次号
                dic.Add("Description", row["productname"].ToString());//零件名称
                dic.Add("SpecNO", row["specno"].ToString());//工序序号
                dic.Add("OPDesc", row["specname"].ToString());//工序名称
                dic.Add("ContainerStepDispatchID", row["containerstepdispatchid"].ToString());//派工id
                dic.Add("State", row["operatetype"].Equals("2") ? "已撤销" : "已报废");//工序状态
                var data = JsonConvert.SerializeObject(dic);
                InsertJsonToTableChangeRecord(data,row,"ZT",toAppId);
            }
        }

        /// <summary>
        /// 保存json文件、同时存储路径到 SYNC_TABLECHANGERECORD
        /// </summary>
        /// <param name="data"></param>
        /// <param name="mdtRow"></param>
        private void InsertJsonToTableChangeRecord(string data, DataRow mdtRow,string fileType,string toAppid)
        {
            var absolutePath = AppDomain.CurrentDomain.BaseDirectory + "files\\jx\\";
            if (!Directory.Exists(absolutePath))
            {
                Directory.CreateDirectory(absolutePath);
            }
            var path = absolutePath + mdtRow["containername"].ToString().Replace("/", "-") + "-" + mdtRow["specname"].ToString() + DateTime.Now.ToString("yyyyMMddhhmmssfff") + fileType + ".json";

            try
            {
                byte[] jsonStr = Encoding.UTF8.GetBytes(data);
                using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(jsonStr, 0, jsonStr.Length);
                    fs.Flush();
                }
                string insertSQl = @"begin insert into SYNC_TABLECHANGERECORD(ID,OLDVALUE,NEWVALUE,OPERATETYPE)values('{0}','{1}','{2}','{3}');
                                    insert into SYNC_SENDTOAPPID(PARENTID,TOAPPID)values('{0}','{4}'); end;";
                OracleHelper.ExecuteSql(string.Format(insertSQl, Guid.NewGuid().ToString(), path, mdtRow["apsplandetailid"].ToString(), mdtRow["operatetype"].ToString(), toAppid));
            }
            catch (Exception ex)
            {

            }
        }

        private void SaveJXWorkflowStepStatus2()
        {
            var sql = @"select st.* from sync_tablechangerecord st 
                        where st.oldvalue is null AND st.OPERATETYPE ='9'
                        and not exists (select 1 from sync_sendtoappid ss where ss.parentid=st.id)
                        order by st.createtime  desc";
            DataTable dt = OracleHelper.QueryTable(sql, null);
            foreach (DataRow row in dt.Rows)
            {
                SaveJsonPathToTableChangeRecord2(row);
            }
        }

        private void SaveJsonPathToTableChangeRecord2(DataRow mdtRow)
        {
            var data = JsonConvert.DeserializeObject<Dictionary<string, string>>(mdtRow["newvalue"].ToString());
            var absolutePath = AppDomain.CurrentDomain.BaseDirectory + "files\\jx\\";
            if (!Directory.Exists(absolutePath))
            {
                Directory.CreateDirectory(absolutePath);
            }
            var path = absolutePath + data["ContainerName"].ToString().Replace("/", "-") + "-" + data["OPDesc"].ToString() + DateTime.Now.ToString("yyyyMMddhhmmssfff") + "ZT.json";

            try
            {
                byte[] jsonStr = Encoding.UTF8.GetBytes(JsonConvert.SerializeObject(data));
                using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(jsonStr, 0, jsonStr.Length);
                    fs.Flush();
                }
                string insertSQl = @"begin update sync_tablechangerecord set oldvalue='{0}' where id='{1}';
                                    insert into sync_sendtoappid(parentid,toappid)values('{1}','{2}'); end;";
                OracleHelper.ExecuteSql(string.Format(insertSQl, path, mdtRow["id"].ToString(), toAppId));
            }
            catch (Exception ex)
            {

            }
        }
        #endregion

        #region IOT工序级任务下发
        public void SaveIOTWorkflowStepData()
        {
            DataTable apsWorkflowStep = getIotApsWorkflowStepData();
            foreach (DataRow row in apsWorkflowStep.Rows)
            {
                DataRow mdtRow = null;
                var data = getWorkflowStepData(row,"", out mdtRow);
                if (mdtRow != null && !string.IsNullOrWhiteSpace(mdtRow["PROCESS_NO"].ToString()))
                {
                   var result= HttpUtils.Post(IotApiUrl, data);
                   LogHelp.WriteLog("IOT工序级任务下发数据：" + JsonConvert.SerializeObject(data)+" 返回结果："+result);
                   OracleHelper.ExecuteSql(string.Format(@"insert into writetoothersystem(writetoothersystemid,parentid,containerid,workflowstepid,issuccess,createdate,recordtype,sendxml,resxml)
                        values(sys_guid(),'{0}','{1}','{2}',1,sysdate,'iot_apsInfoSend','{3}','{4}')", row["containerstepdispatchid"], row["containerid"], row["workflowstepid"], JsonConvert.SerializeObject(data),result)); 
                }
            }
        }

        private DataTable getIotApsWorkflowStepData()
        {
            string sql = @"select cc.containername,cdis.containerstepdispatchid ,cdis.containerid,apd.resourceid,apd.apsplandetailid,c2.customobjectname productline,cc.PRODUCTNAME,w.PROCESS_NO,s.SPECNO,cdis.workflowstepid 
                        ,DECODE(c2.customobjectname,NULL,'D','X') TaskType
                        from containerstepdispatch cdis 
                        left join apsplandetail apd on apd.containerid =cdis.containerid and apd.workflowstepid =cdis.workflowstepid
                        left join containercurrentinfo cc on cc.containerid =cdis.containerid 
                        left join workflowstep ws on ws.workflowstepid =apd.workflowstepid 
                        left join workflow w on w.workflowid =ws.workflowid 
                        left join specbase sb on sb.specbaseid =ws.specbaseid 
                        left join spec s on s.specid =nvl(sb.revofrcdid,ws.specid)
                        LEFT JOIN (
                             SELECT c4.customobjectname,c5.customobjectitemsname 
                            FROM customobject c4 LEFT JOIN customobjectitems c5 ON c5.customobjectid =c4.customobjectid 
                            WHERE c4.customobjectname LIKE 'CX-%'
                        ) c2 on c2.customobjectitemsname=cc.productname
                        left join resourcedef r on r.resourceid =apd.resourceid 
                        where 1=1 and w.process_no IS NOT NULL
                        and apd.isuse=0 and cdis.isuse=0 and apd.status =1
                        and apd.createdate >sysdate - 300 --and cc.containertype =7
                        and not exists (select 1 from writetoothersystem w where w.recordtype='iot_apsInfoSend' and w.parentid=cdis.containerstepdispatchid)
                        and exists (select 1 from ncprogram nc
                        left join ncprogramdetaile ncd on ncd.parentid =nc.id and ncd.equip_code is not null 
                        where nc.productname=cc.productname and nc.process_no=w.process_no 
                        and regexp_like(DECODE(lengthb(nc.specno),1,'000',2,'00',3,'0',4,'','')||nc.specno,s.specno)
                        and ncd.equip_code=r.resourcename)
                        --and DECODE(lengthb(nc.specno),1,'000'||nc.specno,2,'00'||nc.specno,3,'0'||nc.specno,nc.specno)=s.specno)
                        and rownum<500
                        order by cdis.createdate
                       ";
            return OracleHelper.QueryTable(string.Format(sql), null);
        }

        #endregion

        #region IOT分厂人员信息下发
        private void SendIotEmployeeData() 
        {
            string sql = @"SELECT f.FACTORYNAME,e.FULLNAME,e.EMPLOYEENAME FROM  EMPLOYEE e 
                        LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid 
                        LEFT JOIN factory f ON f.factoryid = sv.factoryid 
                        WHERE f.FACTORYID IS NOT NULL 
                        AND NOT EXISTS (SELECT 1 FROM WRITETOOTHERSYSTEM w WHERE w.RECORDTYPE ='iot_employeeSend' AND  REGEXP_LIKE(w.SENDXML,e.EMPLOYEENAME))
                        AND rownum<501 ORDER BY f.FACTORYNAME";
            DataTable dt = OracleHelper.QueryTable(string.Format(sql), null);
            string ApiUrl = @"http://xxx.xx.xx.xx:8010/zjpf-boot-admin/api/permission/Users/SynPdmUser";
            if (dt.Rows.Count>0)
            {
                var employees = string.Join(",", dt.AsEnumerable().Select(r => r.Field<string>("EMPLOYEENAME")));
                List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
                foreach(DataRow row in dt.Rows)
                {
                    Dictionary<string, string> dic = new Dictionary<string, string>();
                    dic.Add("fullName", row["fullName"].ToString());
                    dic.Add("employeeName", row["employeeName"].ToString());
                    dic.Add("factoryName", row["factoryName"].ToString());
                    list.Add(dic);
                }
                var result = string.Empty;
                try
                {
                    result = HttpUtils.Post(ApiUrl, JsonConvert.SerializeObject(list));
                    LogHelp.WriteLog("IOT分厂人员信息下发：" + JsonConvert.SerializeObject(list) + " 返回结果：" + result);
                }
                catch (Exception ex)
                {
                    result = ex.Message;
                    LogHelp.WriteLog("IOT分厂人员信息下发异常：" + ex.Message);
                }
                finally {
                    OracleHelper.ExecuteSql(string.Format(@"insert into writetoothersystem(writetoothersystemid,createdate,recordtype,sendxml,resxml)
                        values(sys_guid(),sysdate,'iot_employeeSend','{0}','{1}')", employees, result));
                }
            }
        }
        #endregion
    }
}
